package com.codeduck.poi.controller.excel;

import com.alibaba.fastjson.JSON;
import com.codeduck.poi.util.AttachmentExportUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * 读写Excel计算公式单元格
 *
 * @author: <a href="mailto:codeduck@163.com">Codeduck</a>
 * @date: 2023/1/31
 */
@RestController
public class FormulaCellController {
    private static final String FILE_PATH = "datafiles/公式单元格数据.xlsx";

    @GetMapping("write/into/formula/cells")
    public void writeToFormulaCell(HttpServletResponse response) {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet("writeDataToFormulaCells");

        Row firstRow = sheet.createRow(0);
        firstRow.createCell(0).setCellValue("A");
        firstRow.createCell(1).setCellValue("B");
        firstRow.createCell(2).setCellValue("A*B");
        firstRow.createCell(3).setCellValue("A＋B");


        Row row = sheet.createRow(1);
        row.createCell(0).setCellValue(10);
        row.createCell(1).setCellValue(20);
        row.createCell(2).setCellFormula("A2*B2");
        row.createCell(3).setCellFormula("SUM(A2:B2)");
        AttachmentExportUtil.export(workbook, "writeDataToFormulaCells", response);
    }

    @GetMapping("read/from/formula/cells")
    public String readDataFromFormulaCell() {
        File file = readFileFromClassPath(FILE_PATH);
        List<String> res = new ArrayList<>();
        try {
            FileInputStream inputStream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            res = readFromFormulaCell(workbook);
        } catch (IOException e) {
            return "文件处理异常";
        }
        return JSON.toJSONString(res);
    }

    @GetMapping("not/read/from/formula/cells")
    public String notReadDataFromFormulaCell() {
        File file = readFileFromClassPath(FILE_PATH);
        List<String> res;
        try {
            FileInputStream inputStream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
            res = notReadFromFormulaCell(workbook);
        } catch (IOException e) {
            return "文件处理异常";
        }
        return JSON.toJSONString(res);
    }

    private List<String> notReadFromFormulaCell(XSSFWorkbook workbook) {
        List<String> res = new ArrayList<>();
        XSSFSheet sheet = workbook.getSheetAt(0); // xssfWorkbook.getSheet("sheet1");

        int rows = sheet.getLastRowNum();   // 总行数
        int cols = sheet.getRow(0).getLastCellNum(); // 总列数

        for (int i = 0; i <= rows; i++) {
            StringBuilder sb = new StringBuilder();
            XSSFRow row = sheet.getRow(i);
            for (int j = 0; j < cols; j++) {
                XSSFCell cell = row.getCell(j);
                if (null == cell) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case STRING: sb.append(cell.getStringCellValue()); break;
                    case NUMERIC: sb.append(cell.getNumericCellValue()); break;   // NUMERIC类型的数据：整数，小数，日期
                    default: sb.append("(BLANK)"); break;
                }
                if (cols != j + 1) {
                    sb.append("\t|\t");
                }
            }
            res.add(sb.toString());
        }
        return res;
    }

    private List<String> readFromFormulaCell(XSSFWorkbook workbook) {
        List<String> res = new ArrayList<>();
        XSSFSheet sheet = workbook.getSheetAt(0); // xssfWorkbook.getSheet("sheet1");

        int rows = sheet.getLastRowNum();   // 总行数
        int cols = sheet.getRow(0).getLastCellNum(); // 总列数

        for (int i = 0; i <= rows; i++) {
            StringBuilder sb = new StringBuilder();
            XSSFRow row = sheet.getRow(i);
            for (int j = 0; j < cols; j++) {
                XSSFCell cell = row.getCell(j);
                if (null == cell) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case STRING: sb.append(cell.getStringCellValue()); break;
                    case NUMERIC: sb.append(cell.getNumericCellValue()); break;   // NUMERIC类型的数据：整数，小数，日期
                    case FORMULA:
//                        方式一
                        sb.append(cell.getNumericCellValue());

//                        方式二
//                        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
//                        sb.append(evaluator.evaluate(cell).getNumberValue());
                        break;     // 判断单元格为 FORMULA类型时，读取会自动转换
                    default: sb.append("(BLANK)"); break;
                }
                if (cols != j + 1) {
                    sb.append("\t|\t");
                }
            }
            res.add(sb.toString());
        }
        return res;
    }

    private File readFileFromClassPath(String filePath) {
        ClassPathResource classPathResource = new ClassPathResource(filePath);
        if (!classPathResource.exists()) {
            return null;
        }
        File file = null;
        try {
            file = classPathResource.getFile();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        return file;
    }
}
